﻿/********************************************************************************
    Copyright (C) Binod Nepal, Planet Earth Solutions Pvt. Ltd., Kathmandu.
	Released under the terms of the GNU General Public License, GPL, 
	as published by the Free Software Foundation, either version 3 
	of the License, or (at your option) any later version.
    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  
    See the License here <http://www.gnu.org/licenses/gpl-3.0.html>.
***********************************************************************************/
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace MixNP.DatabaseLayer
{
    public static class Categories
    {
        public static DataTable GetRootCategories()
        {
            string sql = "SELECT CategoryId, Alias, CategoryName, CategoryDescription, AllowEntry, dbo.GetTotalItems(CategoryId) AS TotalItems FROM dbo.Categories WHERE ParentCategoryId IS NULL ORDER BY CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static DataTable GetCategories(int parentCategoryId)
        {
            string sql = "SELECT CategoryId, Alias, CategoryName, CategoryDescription, AllowEntry, dbo.GetTotalItems(CategoryId) AS TotalItems FROM dbo.Categories WHERE ParentCategoryId=@ParentCategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ParentCategoryId", parentCategoryId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static bool AllowEntry(int categoryId)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Categories WHERE CategoryId=@CategoryId AND AllowEntry=1;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)).Equals("1"));
            }
        }

        public static string GetCategoryAlias(int categoryId)
        {
            string sql = "SELECT Alias FROM dbo.Categories WHERE CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static string GetCategoryName(int categoryId)
        {
            string sql = "SELECT CategoryName FROM dbo.Categories WHERE CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }

        public static int GetCategoryId(string alias)
        {
            string sql = "SELECT CategoryId FROM dbo.Categories WHERE Alias=@Alias;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@Alias", alias);

                return Pes.Utility.Conversion.TryCastInteger(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static int GetParentCategoryId(int categoryId)
        {
            string sql = "SELECT ParentCategoryId FROM dbo.Categories WHERE CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return Pes.Utility.Conversion.TryCastInteger(Pes.DBFactory.DBOperations.GetScalarValue(command));
            }
        }

        public static DataTable GetCustomFields(int categoryId)
        {
            string sql = "SELECT * FROM dbo.CustomFields WHERE CategoryId=@CategoryId AND FieldStatus=1 ORDER BY FieldOrder;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

        public static string GetCustomFieldValue(double itemId, int customFieldId)
        {
            string sql = "SELECT FieldValue FROM dbo.ItemCustomFields WHERE ItemId=@ItemId AND CustomFieldId=@CustomFieldId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@ItemId", itemId);
                command.Parameters.AddWithValue("@CustomFieldId", customFieldId);

                return Pes.DBFactory.DBOperations.GetScalarValue(command).ToString();
            }
        }


        public static bool IsRootCategory(int categoryId)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Categories WHERE CategoryId=@CategoryId AND ParentCategoryId IS NULL;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)).Equals("1"));
            }
        }

        public static bool IsParentCategory(int categoryId, int parentCategoryId)
        {
            string sql = "SELECT TOP 1 1 FROM dbo.Categories WHERE CategoryId=@CategoryId AND ParentCategoryId=@ParentCategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                command.Parameters.AddWithValue("@ParentCategoryId", parentCategoryId);

                return (Pes.Utility.Conversion.TryCastString(Pes.DBFactory.DBOperations.GetScalarValue(command)).Equals("1"));
            }        
        }

        public static DataTable GetCategoryAliasView(double categoryId)
        {
            string sql = "SELECT * FROM dbo.CategoryAliasView WHERE CategoryId=@CategoryId;";
            using (SqlCommand command = new SqlCommand(sql))
            {
                command.Parameters.AddWithValue("@CategoryId", categoryId);
                return Pes.DBFactory.DBOperations.GetDataTable(command);
            }
        }

    }
}
